/* ---------------------------------------------------------------------------------- ◑ SP Name : dbo.USP_ORD_SettleConfirmByReName_Q_DOUZONE 0, '2018-01-01', '2018-01-31', '정산완료', '' ◑ Description : 공급사별 정산예정목록을 조회한다. ◑ Called by : ◑ Input Parameters : ◑ Output Parameters : ◑ Exec : ◑ Change History ---------------------------------------------------------------------------------- Date Author Description ---------------------------------------------------------------------------------- 2018-03-06 성근영 최초 생성 2019-06-11 이선미 정산액 수정 및튜닝 (임시테이블 제거, 그룹바이 중복 제거) 2019-07-02 이선미 정산액 마이너스 금액도 포함 ---------------------------------------------------------------------------------- */ CREATE PROCEDURE [dbo].[USP_ORD_SettleConfirmByReName_Q_DOUZONE] @ProviderNo INT, @DateFrom DATETIME, @DateTo DATETIME, @SettleStatusCd NVARCHAR(20), @SettleDate CHAR(10) AS BEGIN SET NOCOUNT ON; DECLARE @StrDateFrom Nvarchar(10), @StrDateTo Nvarchar(10) SET @StrDateFrom = Convert(Nvarchar(10), @DateFrom, 121) SET @StrDateTo = Convert(Nvarchar(10), DateAdd(day, 1, @DateTo), 121) ;WITH CTE_Settle(BizNumber,settleDate, CouponPrice, CouponAllotment, OrderSettleAmount, MarginSettleAmount, Amount) AS( SELECT V.BizNumber , V.SettleDate , SUM(V.CouponPrice) AS CouponPrice , SUM(V.CouponAllotment) AS CouponAllotment , SUM(V.OrderSettleAmount) AS OrderSettleAmount , SUM(V.MarginSettleAmount) AS MarginSettleAmount , SUM(V.OrderSettleAmount + V.CouponPrice - V.MarginSettleAmount-V.CouponAllotment) As Amount --, CAST(ROUND(SUM(V.MarginSettleAmount / 1.1),4) AS float) As SupAmount --, CAST(ROUND(SUM(V.MarginSettleAmount) - SUM(V.MarginSettleAmount / 1.1),4) AS float) As Tax FROM ( SELECT VD.BizNumber , V.SettleDate , V.SettleStatusCd , V.VendorNo , V.CouponPrice , V.CouponAllotment , V.OrderSettleAmount ,V.MarginSettleAmount FROM dbo.VW_ORD_Settlement AS V WITH (NOLOCK) INNER JOIN dbo.TB_VEN_Provider AS P WITH (NOLOCK) ON V.ProviderNo = P.ProviderNo INNER JOIN dbo.TB_VEN_Vendor AS VD WITH (NOLOCK) ON P.VendorNo = VD.VendorNo WHERE V.DateSales >= @StrDateFrom AND V.DateSales < @StrDateTo AND CASE WHEN @SettleStatusCd = '' THEN '' ELSE SettleStatusCd END = @SettleStatusCd AND CASE WHEN @SettleDate = '' THEN '' ELSE SettleDate END = @SettleDate ) V GROUP BY V.VendorNo, V.BizNumber, V.SettleDate, V.SettleStatusCd ) --더존계산식 SELECT BizNumber, SettleDate , CAST(ROUND((OrderSettleAmount) * -1 ,0) AS numeric(19, 4)) AS '상품매출액' , CAST(ROUND(MarginSettleAmount -( CouponPrice- CouponAllotment), 0) - ROUND((MarginSettleAmount -( CouponPrice- CouponAllotment)) / 1.1, 0) AS numeric(19,4)) AS '예수부가세' , CAST(ROUND(((MarginSettleAmount -( CouponPrice- CouponAllotment)) / 1.1), 0) AS numeric(19, 4)) AS '수수료매출' , CAST((Amount) AS numeric(19, 4)) AS '미지급금' FROM CTE_Settle; --WHERE OrderSettleAmount > 0 /* DECLARE @TargetListTable Table ( StrDateFrom Nvarchar(10) , StrDateTo Nvarchar(10) , BizNumber Nvarchar(50) , SettleDate Nvarchar(10) , SettleStatusCd Nvarchar(20) , CouponPrice Money , CouponAllotment Money , OrderSettleAmount Money , MarginSettleAmount Money , Amount Money , SupAmount Money , Tax Money ) INSERT INTO @TargetListTable SELECT @StrDateFrom, @StrDateTo, V.BizNumber, V.SettleDate, V.SettleStatusCd , SUM(V.CouponPrice) AS CouponPrice , SUM(V.CouponAllotment) AS CouponAllotment , SUM(V.OrderSettleAmount) AS OrderSettleAmount , SUM(V.MarginSettleAmount) AS MarginSettleAmount , SUM(V.OrderSettleAmount + V.CouponPrice - V.MarginSettleAmount) As Amount , SUM(V.MarginSettleAmount / 1.1) As SupAmount , SUM(V.MarginSettleAmount) - SUM(V.MarginSettleAmount / 1.1) As Tax FROM ( SELECT V.VendorNo, V.VendorName, V.ProviderNo, V.ProviderName, V.DeliveryUnitNo, V.DeliveryUnitName, VD.Representative, VD.BizNumber, P.BankAccountInfo, V.SettleDate, V.SettleStatusCd , SUM(V.CouponPrice) AS CouponPrice, SUM(V.CouponAllotment) AS CouponAllotment, SUM(V.OrderSettleAmount) AS OrderSettleAmount , ROUND(SUM(V.MarginSettleAmount),0) AS MarginSettleAmount , SUM((V.OrderSettleAmount+V.CouponPrice) - V.MarginSettleAmount) As Amount , ROUND(ROUND(SUM(V.MarginSettleAmount),0) / 1.1, 0) As SupAmount , ROUND(SUM(V.CouponPrice+V.OrderSettleAmount),0) as StandardTotalPrice , ROUND(SUM(V.MarginSettleAmount),0) - ROUND(ROUND(SUM(V.MarginSettleAmount),0) / 1.1, 0) As Tax , SUM(V.OrderSurTax) AS OrderSurTax, ROUND(SUM(V.MarginSurTax),0) AS MarginSurTax FROM dbo.VW_ORD_Settlement AS V WITH (NOLOCK) INNER JOIN dbo.TB_VEN_Provider AS P WITH (NOLOCK) ON V.ProviderNo = P.ProviderNo INNER JOIN dbo.TB_VEN_Vendor AS VD WITH (NOLOCK) ON P.VendorNo = VD.VendorNo WHERE V.DateSales >= @StrDateFrom AND V.DateSales < @StrDateTo GROUP BY V.VendorNo, V.VendorName, V.ProviderNo, V.ProviderName, V.DeliveryUnitNo, V.DeliveryUnitName, VD.Representative, VD.BizNumber, P.BankAccountInfo, V.SettleDate, V.SettleStatusCd ) V --FROM dbo.VW_ORD_Settlement AS V WITH (NOLOCK) -- INNER JOIN dbo.TB_VEN_Vendor AS VD WITH (NOLOCK) ON V.VendorNo = VD.VendorNo --WHERE V.DateSales >= @StrDateFrom --AND V.DateSales < @StrDateTo GROUP BY V.VendorNo, V.BizNumber, V.SettleDate, V.SettleStatusCd If @SettleStatusCd <> '' DELETE FROM @TargetListTable WHERE SettleStatusCd <> @SettleStatusCd If @SettleDate <> '' DELETE FROM @TargetListTable WHERE SettleDate <> @SettleDate SELECT BizNumber, SettleDate, CouponPrice, CouponAllotment, OrderSettleAmount , MarginSettleAmount, Amount, SupAmount, Tax FROM @TargetListTable */ SET NOCOUNT OFF; END